insert overwrite table jms_dm.dm_center_detention_count_dt partition(dt)
select  --中心汇总
    site_agent_code
     ,max(site_agent_name) as site_agent_name
     ,scan_site_code
     ,max(scan_site) as scan_site
     ,sum(cnt_48h)  as cnt_48h
     ,sum(cnt_72h)  as cnt_72h
     ,sum(cnt_96h)  as cnt_96h
     ,sum(cnt_120h) as cnt_120h
     ,sum(cnt_144h) as cnt_144h
     ,sum(cnt_168h) as cnt_168h
--,sum(intercept_cnt) intercept_cnt     --筛单票数
     ,sum(cnt) as cnt  --总票数
     ,sum(intercept_yiqing_cnt) as intercept_yiqing_cnt  --筛单疫情票数
     ,sum(intercept_other_cnt  ) as  intercept_other_cnt --筛单非疫情票数
     ,sum(intercept_not_cnt    ) as  intercept_not_cnt   --筛单未录入问题件票数
     ,sum(notin_yiqing_cnt     ) as  notin_yiqing_cnt    --非筛单疫情票数
     ,sum(notin_other_cnt      ) as  notin_other_cnt     --非筛单非疫情票数
     ,sum(notin_not_cnt        ) as  notin_not_cnt       --非筛单未录入问题件票数
     ,'site' as cnt_type
     ,'{{ execution_date | cst_ds }}' as date_time
     ,'{{ execution_date | cst_ds }}' as dt
from (
         select
             scan_date
              ,site_agent_code
              ,site_agent_name
              ,scan_site_code
              ,scan_site
              ,cnt
              ,hour_type
              ,case    when hour_type='48h'  then cnt else 0 end as cnt_48h
              ,case    when hour_type='72h'  then cnt else 0 end as cnt_72h
              ,case    when hour_type='96h'  then cnt else 0 end as cnt_96h
              ,case    when hour_type='120h' then cnt else 0 end as cnt_120h
              ,case    when hour_type='144h' then cnt else 0 end as cnt_144h
              ,case    when hour_type='168h' then cnt else 0 end as cnt_168h
              ,intercept_cnt
              ,intercept_yiqing_cnt
              ,intercept_other_cnt
              ,intercept_not_cnt
              ,notin_yiqing_cnt
              ,notin_other_cnt
              ,notin_not_cnt
         from
             (
                 select
                     date(scan_time) scan_date
            ,site_agent_code
            ,max(site_agent_name) as site_agent_name
            ,scan_site_code
            ,max(scan_site) as scan_site
            ,hour_type
            ,count(1) as cnt
            ,sum(is_intercepte) as intercept_cnt  --筛单票数-总票数
            ,sum(if(is_intercepte=1 and is_yiqing_problem=1,1,0)) as intercept_yiqing_cnt                                       --筛单疫情票数
            ,sum(if(is_intercepte=1 and is_yiqing_problem=0 and second_level_type_code is not null,1,0)) as intercept_other_cnt --筛单非疫情票数
            ,sum(if(is_intercepte=1 and is_yiqing_problem=0 and second_level_type_code is  null,1,0)) as intercept_not_cnt      --筛单未录入问题件票数
            ,sum(if(is_intercepte!=1 and is_yiqing_problem=1,1,0)) as notin_yiqing_cnt                                          --非筛单疫情票数
            ,sum(if(is_intercepte!=1 and is_yiqing_problem=0 and second_level_type_code is not null,1,0)) as notin_other_cnt --非筛单非疫情票数
            ,sum(if(is_intercepte!=1 and is_yiqing_problem=0 and second_level_type_code is null,1,0)) as notin_not_cnt      --非筛单未录入问题件票数
             from jms_dm.dm_center_detention_detail_dt
         where dt ='{{ execution_date | cst_ds }}'
           and hour_type!='小于48h'
         group by date(scan_time),site_agent_code,scan_site_code,hour_type
     )t
    )tt group by site_agent_code,scan_site_code

union all

select  --代理区汇总
    site_agent_code
     ,max(site_agent_name) as site_agent_name
     ,null as scan_site_code
     ,null as scan_site
     ,sum(cnt_48h)  as cnt_48h
     ,sum(cnt_72h)  as cnt_72h
     ,sum(cnt_96h)  as cnt_96h
     ,sum(cnt_120h) as cnt_120h
     ,sum(cnt_144h) as cnt_144h
     ,sum(cnt_168h) as cnt_168h
     ,sum(cnt) as cnt
     ,sum(intercept_yiqing_cnt) intercept_yiqing_cnt
     ,sum(intercept_other_cnt  ) as  intercept_other_cnt
     ,sum(intercept_not_cnt    ) as  intercept_not_cnt
     ,sum(notin_yiqing_cnt     ) as  notin_yiqing_cnt
     ,sum(notin_other_cnt      ) as  notin_other_cnt
     ,sum(notin_not_cnt        ) as  notin_not_cnt
     ,'agent' as cnt_type
     ,'{{ execution_date | cst_ds }}' as date_time
     ,'{{ execution_date | cst_ds }}' as dt
from (
         select
             scan_date
              ,site_agent_code
              ,site_agent_name
              ,scan_site_code
              ,scan_site
              ,cnt
              ,hour_type
              ,case    when hour_type='48h'  then cnt else 0 end as cnt_48h
              ,case    when hour_type='72h'  then cnt else 0 end as cnt_72h
              ,case    when hour_type='96h'  then cnt else 0 end as cnt_96h
              ,case    when hour_type='120h' then cnt else 0 end as cnt_120h
              ,case    when hour_type='144h' then cnt else 0 end as cnt_144h
              ,case    when hour_type='168h' then cnt else 0 end as cnt_168h
              ,intercept_cnt
              ,intercept_yiqing_cnt
              ,intercept_other_cnt
              ,intercept_not_cnt
              ,notin_yiqing_cnt
              ,notin_other_cnt
              ,notin_not_cnt
         from
             (
                 select
                     date(scan_time) scan_date
            ,site_agent_code
            ,max(site_agent_name) as site_agent_name
            ,scan_site_code
            ,max(scan_site) as scan_site
            ,hour_type
            ,count(1) as cnt
            ,sum(is_intercepte) as intercept_cnt  --筛单票数-总票数
            ,sum(if(is_intercepte=1 and is_yiqing_problem=1,1,0)) as intercept_yiqing_cnt                                       --筛单疫情票数
            ,sum(if(is_intercepte=1 and is_yiqing_problem=0 and second_level_type_code is not null,1,0)) as intercept_other_cnt --筛单非疫情票数
            ,sum(if(is_intercepte=1 and is_yiqing_problem=0 and second_level_type_code is  null,1,0)) as intercept_not_cnt      --筛单未录入问题件票数
            ,sum(if(is_intercepte!=1 and is_yiqing_problem=1,1,0)) as notin_yiqing_cnt                                          --非筛单疫情票数
            ,sum(if(is_intercepte!=1 and is_yiqing_problem=0 and second_level_type_code is not null,1,0)) as notin_other_cnt --非筛单非疫情票数
            ,sum(if(is_intercepte!=1 and is_yiqing_problem=0 and second_level_type_code is null,1,0)) as notin_not_cnt      --非筛单未录入问题件票数
             from jms_dm.dm_center_detention_detail_dt
         where dt ='{{ execution_date | cst_ds }}'
           and hour_type!='小于48h'
         group by date(scan_time),site_agent_code,scan_site_code,hour_type
     )t
    )tt group by site_agent_code
distribute by cnt_type
;